package com.situ.wms.dao;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import com.situ.wms.entity.Goods;
import com.situ.wms.entity.GoodsNotes;
import com.situ.wms.entity.Manager;
import com.situ.wms.util.DBUtil;



public class ManagerDao {
	//用来查询所有的商品
	public List<Goods> select(int page, int limit,String keyword){ 
		String sql;
		if(keyword==null||keyword.equals("")) {
		sql="select * from goods  limit ? ,? ";}
		else {
			sql="select * from goods where instr(name,'"+keyword+"')>0 limit ? , ? ";
		}
		return DBUtil.executeDQL(sql, Goods.class,(page-1)*limit ,limit);
	}
	
	//商品出库
	public void delete(int id) {
		String sql="delete from goods where id = ?";
		DBUtil.executeDML(sql,id);
		
	}
	
	//商品入库
	public void add(Goods goods) {
		String sql="insert into goods (name,factory,model,size,number) values(?,?,?,?,?) ";
		String name=goods.getName();
		String factory=goods.getFactory();
		String model=goods.getModel();
		String size=goods.getSize();
		int number=goods.getNumber();
		DBUtil.executeDML(sql,name,factory,model,size,number);
	}


	public void update(Goods goods) {
		
		String sql ="update goods set name =? , factory=? , model=? , size=? , number=? where id =?";
	 	DBUtil.executeDML(sql,goods.getName(),goods.getFactory(),goods.getModel(),goods.getSize(),goods.getNumber(),goods.getId());
	}
	
	public Long count( String keyword) {
		String sql;
		if(keyword==null||keyword.equals("")) {
			sql="select count(1) from goods";
			}
			else {
				
				sql="select count(1) from goods where instr(name,'"+keyword+"')>0 ";
			}
		
		return DBUtil.executeCount(sql);
	}

	public Manager selectByusername(String username) {
		String sql="select * from Manager where username =?";
		List<Manager> list= DBUtil.executeDQL(sql, Manager.class,username);
		if(list.size()>0)
		return list.get(0);
		else return null;
	}
	public Goods selectByid(int id) {
		String sql="select * from goods where id=?";
		List<Goods> list= DBUtil.executeDQL(sql, Goods.class,id);
		if(list.size()>0)
			return list.get(0);
			else return null;
		
	}

	public BigDecimal insumbyyear(String year) {
		if(year.length()!=4) {
			return null;
		}else {
		String sql="SELECT sum(goodsnumber) from goodsnotes where DATE_FORMAT(actiondate,'%Y')='"+year+"'  and `status`= '入库'";
		return DBUtil.executeSum(sql);
	}
		
}

	public BigDecimal insumbymonth(String month) {
		if(month.length()!=7) {
			return null;
		}else {
		String sql="SELECT sum(goodsnumber) from goodsnotes where DATE_FORMAT(actiondate,'%Y-%m')='"+month+"'  and `status`= '入库'";
		return DBUtil.executeSum(sql);}
	}

	public BigDecimal insumbyday(String day) {
		if(day.length()!=10) {
		
			return null;
		}else {
		String sql="SELECT sum(goodsnumber) from goodsnotes where actiondate ='"+day+"'  and `status`= '入库'";
	
		return DBUtil.executeSum(sql);}
	}

	public BigDecimal outsumbyyear(String year) {
		if(year.length()!=4) {
			return null;
		}else {
		String sql="SELECT sum(goodsnumber) from goodsnotes where DATE_FORMAT(actiondate,'%Y')='"+year+"'  and `status`= '出库'";
		return DBUtil.executeSum(sql);
	}
	}

	public BigDecimal outsumbymonth(String month) {
		if(month.length()!=7) {
		
			return null;
		}else {
			
		String sql="SELECT sum(goodsnumber) from goodsnotes where DATE_FORMAT(actiondate,'%Y-%m')='"+month+"'  and `status`= '出库'";
		return DBUtil.executeSum(sql);}
	}

	public BigDecimal outsumbyday(String day) {
		if(day.length()!=10) {
			
			return null;
		}else {
			
		String sql="SELECT sum(goodsnumber) from goodsnotes where actiondate ='"+day+"'  and `status`= '出库'";
		System.out.println(day);
		return DBUtil.executeSum(sql);}
	}
}